﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using Inicio.Entities;
using System.Windows.Forms;

namespace Inicio.Core.Repositories
{
    public class ObraRepositorie
    {
        public List<Obra> LoadObras(string conec)
        {
            List<Obra> obras = new List<Obra>();

            using (SqlCeConnection cn = new SqlCeConnection(conec))
            {
                cn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand(
                    "SELECT id, obraName FROM Obra ORDER BY obraName ASC", cn))
                {
                    using (SqlCeDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            obras.Add(new Obra((int)reader["id"], reader["obraName"].ToString()));
                        }
                    }
                }
            }
            return obras;
        }

        public void NewObra(Obra obra, string conn, List<Spout> spouts)
        {
            int currentId = 0;

            try
            {
                using (SqlCeConnection cn = new SqlCeConnection(conn))
                {
                    cn.Open();
                    SqlCeCommand cmd = new SqlCeCommand(
                        "INSERT INTO Work (workName, lenderId, workTypeId, contractNumber, "
                    + "countryId, provinceId, city, principal, constructionCompany, "
                    + "subContractor, inspection, initiation, finish, amount, currency) "
                    + "VALUES(@workName, @lenderId, @workTypeId, @contractNumber, @countryId, "
                    + "@provinceId, @city, @principal, @constructionCompany, @subContractor, "
                    + "@inspection, @initiation, @finish, @amount, @currency);", cn);

                    cmd.Parameters.Add(new SqlCeParameter("workName", obra.obraName));
                    cmd.Parameters.Add(new SqlCeParameter("lenderId", obra.obraType));
                    cmd.Parameters.Add(new SqlCeParameter("workTypeId", obra.style));
                    cmd.Parameters.Add(new SqlCeParameter("contractNumber", obra.contractNumber));
                    cmd.Parameters.Add(new SqlCeParameter("countryId", obra.countryId));
                    cmd.Parameters.Add(new SqlCeParameter("provinceId", obra.provinceId));
                    cmd.Parameters.Add(new SqlCeParameter("city", obra.city));
                    cmd.Parameters.Add(new SqlCeParameter("principal", obra.comitente));
                    cmd.Parameters.Add(new SqlCeParameter("constructionCompany", obra.constructionCompany));
                    cmd.Parameters.Add(new SqlCeParameter("subContractor", obra.subContractor));
                    cmd.Parameters.Add(new SqlCeParameter("inspection", obra.inspection));
                    cmd.Parameters.Add(new SqlCeParameter("initiation", obra.initiation));
                    cmd.Parameters.Add(new SqlCeParameter("finish", obra.finish));
                    cmd.Parameters.Add(new SqlCeParameter("currency", obra.currency));
                    cmd.Parameters.Add(new SqlCeParameter("amount", obra.amount));

                    cmd.ExecuteNonQuery();
                    //currentId = Convert.ToInt32(cmd.ExecuteScalar());

                    SqlCeCommand actualId = new SqlCeCommand("Select @@IDENTITY", cn);
                    currentId = Convert.ToInt32(actualId.ExecuteScalar());
                }
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show(ex.Message);
            }


            foreach (Spout spout in spouts)
            {
                try
                {
                    spout.obraId = currentId;

                    using (SqlCeConnection sConn = new SqlCeConnection(conn))
                    {
                        sConn.Open();
                        SqlCeCommand sCmd = new SqlCeCommand(
                            "INSERT INTO Spout (workId, diameter, pressure, rigidity, longitude) "
                            + "VALUES("
                            + "@obraId, @diameter, @pressure, @rigidity, @lenght)", sConn);

                        sCmd.Parameters.Add(new SqlCeParameter("obraId", spout.obraId));
                        sCmd.Parameters.Add(new SqlCeParameter("diameter", spout.diameter));
                        sCmd.Parameters.Add(new SqlCeParameter("pressure", spout.pressure));
                        sCmd.Parameters.Add(new SqlCeParameter("rigidity", spout.rigidity));
                        sCmd.Parameters.Add(new SqlCeParameter("lenght", spout.length));

                        int i = sCmd.ExecuteNonQuery();
                    }
                }
                catch (System.Exception ex) { MessageBox.Show(ex.Message); }
            }
            if (currentId != 0)
            {
                MessageBox.Show("La obra fue agendada con éxito.");
                //this.Dispose();
                //this.Close();
            }
        }


    }
}
